create database dwd;
use dwd;
create table dwd.dwd_intention_customer_relationship_i(
    id int COMMENT '客户关系id',
    create_date_time STRING COMMENT '创建时间',
    year_date STRING COMMENT '年',
    month_date STRING COMMENT '月',
    day_date STRING COMMENT '日',
    hour_date STRING COMMENT '时',
    deleted STRING COMMENT '是否被删除（禁用）',
    customer_id int COMMENT '所属客户id',
    origin_type int COMMENT '数据来源 0线下 1线上',
    itcast_school_id int COMMENT '校区Id',
    itcast_subject_id int COMMENT '学科Id',
    creator int COMMENT '创建人'
)comment '客户意向时表'
PARTITIONED BY(dt STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');


create table dwd.dwd_intention_customer_clue_i(
     id int COMMENT 'customer_clue_id',
     deleted STRING COMMENT '是否被删除（禁用）',
     customer_relationship_id int COMMENT '客户关系id',
     clue_state int COMMENT '线索状态 0老 1新'
)comment '客户线索表'
PARTITIONED BY(dt STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

create table dwd.dwd_clue_customer_appeal_i(
     id int COMMENT '主键',
     customer_relationship_first_id int COMMENT '第一条客户关系id',
     appeal_status int COMMENT '申诉状态，0:待稽核 1:无效 2：有效'
)comment '线索申诉表'
PARTITIONED BY(dt STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

insert overwrite table dwd.dwd_clue_customer_appeal_i partition (dt)
select id,customer_relationship_first_id,appeal_status,dt from ods.ods_clue_customer_appeal_i;

insert overwrite table dwd.dwd_intention_customer_clue_i partition (dt)
select
id,deleted,customer_relationship_id,
case when clue_state = 'VALID_PUBLIC_NEW_CLUE' or clue_state = 'VALID_NEW_CLUES' then 1 else 0 end clue_state,dt
from ods.ods_intention_customer_clue_i where deleted = 'false';

insert overwrite  table dwd.dwd_intention_customer_relationship_i partition (dt)
select
id,date_format(create_date_time,'yyyy-MM-dd'),
year(create_date_time),
month(create_date_time),
day(create_date_time),
hour(create_date_time),
deleted,customer_id,
case when origin_type in ('NETSERVICE','PRESIGNUP') then 1 else 0 end,
case when itcast_school_id = 0 or itcast_school_id is null then -1 else itcast_school_id end itcast_school_id,
case when itcast_subject_id = 0 or itcast_subject_id is null then -1 else itcast_subject_id end itcast_subject_id,
creator,dt
from ods.ods_intention_customer_relationship_i where deleted = 'false';